﻿
/****** Object:  StoredProcedure [dbo].[Itinerary_updateitinerary]    Script Date: 05/08/2008 09:52:43 ******/
CREATE PROC [dbo].[Itinerary_updateitinerary]
	@name nvarchar(256),
	@major bigint,
	@minor bigint,
	@itml xml,
	@status tinyint = 1
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET ANSI_PADDING ON

DECLARE @key bigint
SELECT @key = nItinerarySetID
	FROM [dbo].[Itinerary]
	WHERE strName = @name AND DATALENGTH(strName) = DATALENGTH(@name) AND nMajor = @major AND nMinor = @minor
IF @@ROWCOUNT = 0
	RETURN -1

UPDATE [dbo].[Itinerary]
	SET imITML = @itml
	WHERE strName = @name AND DATALENGTH(strName) = DATALENGTH(@name) AND nMajor = @major AND nMinor = @minor AND nStatus = @status

-- Clear and Re-Add rows to the Itinerary Service table
DELETE FROM [dbo].[ItineraryService]
WHERE
	@name = [ItineraryService].[ItineraryName]
	AND @major = [ItineraryService].[ItineraryMajorVersion]
	AND @minor = [ItineraryService].[ItineraryMinorVersion]

DECLARE @modifiedby nvarchar(256)
DECLARE @modifiedon datetime
SET @modifiedby = (SELECT [strModifiedBy] FROM [dbo].[Itinerary]
	WHERE strName = @name AND DATALENGTH(strName) = DATALENGTH(@name)
	AND nMajor = @major AND nMinor = @minor AND nStatus = @status) 
SET @modifiedon = (SELECT [dtModifiedTime] FROM [dbo].[Itinerary]
	WHERE strName = @name AND DATALENGTH(strName) = DATALENGTH(@name)
	AND nMajor = @major AND nMinor = @minor AND nStatus = @status) 

INSERT INTO [dbo].[ItineraryService] ([ServiceUuid], [Name], [Type], [IsRequestResponse], [Position], [Stage], [Id], [NextId], [BusinessName],
		[ItineraryName],
		[ItineraryMajorVersion],
		[ItineraryMinorVersion],
		[ModifiedBy],
		[ModifiedOn])
	SELECT 
		Svc.value('(@uuid)[1]', 'NVARCHAR(50)') as 'ServiceUuid',
		Svc.value('(@name)[1]', 'NVARCHAR(100)') as 'ServiceName',
		Svc.value('(@type)[1]', 'NVARCHAR(50)') as 'Type',
		Svc.value('(@isRequestResponse)[1]', 'bit') as 'IsRequestResponse',
		Svc.value('(@position)[1]', 'int') as 'Position',
		Svc.value('(@stage)[1]', 'NVARCHAR(50)') as 'Stage',
		Svc.value('(@id)[1]', 'NVARCHAR(50)') as 'Id',
		Svc.value('(@nextId)[1]', 'NVARCHAR(50)') as 'NextId',
		Svc.value('(@businessName)[1]', 'NVARCHAR(250)') as 'BusinessName',
		@name as 'ItineraryName',
		@major as 'ItineraryMajor',
		@minor as 'ItineraryMinor',
		@modifiedby as 'ModifiedBy',
		@modifiedon as 'ModifiedOn'
	FROM
		@itml.nodes('//Service') as Itin(Svc)

RETURN @key
